﻿create proc sp_SelectLichDiByMaChuyen
	@machuyen char(10)
as
	select * from LichDi where MACHUYEN=@machuyen

alter proc sp_SelectLichDi
@ngayDi Datetime,
@maChuyen char(10),
@soHieu char(10)
as
	select * from LichDi Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu

----------------------
create proc sp_SelectALLLichDi	
as
	select * from LichDi

alter proc usp_XoaLichDi
@ngayDi Datetime,
@maChuyen char(10),
@soHieu char(10)
as
	begin try
		DELETE FROM DangKy Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu
		DELETE FROM LichDi Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu
	end try
	begin catch
		declare @error varchar(1000)
		set @error = N'Xóa thất bại'
		raiserror (@error,16,1)
		rollback tran
		return
	end catch
go


Create proc usp_ThemLichDi
@ngayDi Datetime,
@maChuyen char(10),
@soHieu char(10),
@maLoai char(4),
@soTai int,
@soLo int,
@tthienTai int,
@tinhTrang char(5)
as
	begin try
		INSERT INTO LICHDI VALUES(@ngayDi,@maChuyen,@soHieu,@maLoai,@soTai,@soLo,@tthienTai,@tinhTrang)
	end try
	begin catch
		declare @error varchar(1000)
		set @error = N'Thêm thất bại'
		raiserror (@error,16,1)
		rollback tran
		return
	end catch
go


create proc usp_SuaLichDi
@ngayDi Datetime,
@maChuyen char(10),
@soHieu char(10),
@maLoai char(4),
@soTai int,
@soLo int,
@tthienTai int,
@tinhTrang char(5)
as
	begin try
		UPDATE LICHDI SET MaLoai =@maLoai,SoTai = @soTai,SoLo = @soLo,TTHienTai = @tthienTai,TinhTrang = @tinhTrang Where NgayDi = @ngayDi AND MaChuyen = @maChuyen AND SoHieu = @soHieu
	end try
	begin catch
		declare @error varchar(1000)
		set @error = N'Sửa thất bại'
		raiserror (@error,16,1)
		rollback tran
		return
	end catch
go


Create proc sp_SelectALLChuyenXe
as
	select * from chuyenxe

create proc sp_SelectKhachHangALL
as 
	select * from khachhang
Create proc sp_SelectALLLoaiXe
as
	select * from LOAIXE
create proc sp_SelectLichDiByMaLoai
	@maloai char(4)
as
	select * from LichDi where MALOAI=@maloai
create proc sp_SelectChuyenXeByMaChuyen
	@machuyen char(10)
as
	select * from CHUYENXE where MACHUYEN=@machuyen

create proc sp_SelectALLDatCho
as
	select * from DATCHO

create proc sp_SelectChuyenXeByBXDen
	@BDen nvarchar(50)
as
	select * from CHUYENXE where BENXEDEN=@BDen

create proc sp_SelectNhanVienByMaNV
	@manv char(10)
as
	select * from NHANVIEN where MANV=@manv
create proc sp_SelectALLNhanVien
as
	select * from NHANVIEN
create proc sp_SelectNhanVienByMaNVPASS
	@manv char(10),@pass char(10),@c int output
as
	set @c=(select count(*) from NHANVIEN where MANV=@manv and MKHAU=@pass)

alter proc sp_SelectNhanVienByMaNVPASS
	@manv char(10),@pass char(10)
	as
	select * from NHANVIEN where MANV=@manv and MKHAU=@pass


	Declare @a int
	exec sp_SelectNhanVienByMaNVPASS 'QL22222221','123456', @a out
	print @a

-------
create proc sp_SelectALLDangKy	
as
	select * from DANGKY

create proc sp_SelectDangKyByNGAYDI
	@ngayDi datetime
as
	select * from DANGKY where NgayDi = @ngayDi
----
----select unrepeattable read---
Create proc usp_xemchuyenxetheoma
@ma char(10)
as
begin tran
	select MACHUYEN from CHUYENXE
	waitfor delay '0:0:10'
	select * from CHUYENXE where MACHUYEN =@ma
commit tran
---them chuyen xe----
Create Proc usp_themchuyenxe
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as
begin 
	if(exists (select MACHUYEN from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Mã chuyến đã có'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
	insert into CHUYENXE values(@ma,@bdi,@bden,@giodi,@gioden)
	print N'Thêm Thành Công'
end

exec usp_themchuyenxe '104','A','S','21:00:00','23:59:59'
select * from CHUYENXE
----xoa chuyen xxe------
create proc usp_xoachuyenxe
@ma char(10)
as
begin
	if(not exists (select * from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Chuyến xe không tồn tại'
		return
	end
	delete DATCHO where MACHUYEN=@ma
	delete DANGKY where MACHUYEN=@ma
	delete LICHDI where MACHUYEN=@ma
	delete CHUYENXE where MACHUYEN=@ma
	print N'Xóa Thành Công'

end

exec usp_xoachuyenxe '100'
select * from lichdi
select * from CHUYENXE
--------update chuyến xe---------
Create proc usp_capnhatchuyenxe
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as 
begin
	if(not exists (select * from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Chuyến xe không tồn tại'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
		update CHUYENXE set MACHUYEN=@ma,BENXEDI=@bdi,BENXEDEN=@bden,GIODI=@giodi,GIODEN=@gioden 
		where MACHUYEN=@ma
	print N'Update thành công'

end
exec usp_capnhatchuyenxe '100','t','S21','21:00:00','23:59:59'
select * from CHUYENXE

-------Phan Tom-----------
--------dem so luong chuyen trong lich di--------
USE [QLLVT]
GO
/****** Object:  StoredProcedure [dbo].[usp_Soluongchuyen]    Script Date: 10/2/2013 6:31:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[usp_Soluongchuyen]

@chuyen nchar(20),
@ngaybd datetime,
@ngaykt datetime,
@tong int output
As
begin tran
set tran isolation level serializable
begin try
	if(not exists( select * from LICHDI where MACHUYEN=@chuyen))
	begin
		print N'Khong ton tai chuyen xe co ma la '+@chuyen
		return
	end
		
		set @tong= (select count(MACHUYEN) from LICHDI where MACHUYEN=@chuyen and NGAYDI between @ngaybd and @ngaykt)
		waitfor delay '00:00:10'
		select * from LICHDI where MACHUYEN=@chuyen and NGAYDI between @ngaybd and @ngaykt
	 
end try
BEGIN CATCH 
			DECLARE @ErrorMsg VARCHAR(2000)
			SELECT @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
			RAISERROR(@ErrorMsg, 16,1)
			ROLLBACK TRAN
			RETURN
END CATCH 
commit


declare @tong int
exec usp_Soluongchuyen '100','11/11/2013','11/27/2013', @tong out
print @tong

select * from LICHDI where MACHUYEN='100'

------insert lich di----
CREATE proc [dbo].[usp_themlich]
@Ngay datetime,
@Chuyen char(15),
@So char(15),
@loai char(15),
@sotai int,
@solo int
As
Begin
Begin try
if (not exists (select * from CHUYENXE where MACHUYEN = @Chuyen))
	begin
		print 'Mã chuyến ' +@chuyen + ' Khong Ton Tai'
		return
	end
if (exists (select * from LICHDI where @Ngay=NGAYDI AND @Chuyen=MACHUYEN AND @SO=SOHIEU AND @loai=MALOAI))
	begin
		print 'Lich di nay da co'
		return
	end
if (not exists (select * from LOAIXE where MALOAI = @loai))
	begin
		print 'Loại xe ' +@loai + ' Khong Ton Tai'
		return
	end
if (not exists (select SOHIEU from XE where MALOAI=@loai AND SOHIEU=@So))
	begin
		print ' hieu xe ko co'
		return
	end
insert into LICHDI values(@Ngay,@Chuyen,@So,@loai,@sotai,@solo, '0','NO')
END  try
BEGIN CATCH 
			DECLARE @ErrorMsg VARCHAR(2000)
			SELECT @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
			RAISERROR(@ErrorMsg, 16,1)
			ROLLBACK TRAN
			RETURN
END CATCH 
end

------------------
----select unrepeattable read---
Create proc usp_xemchuyenxetheoma
@ma char(10)
as
begin tran
	select MACHUYEN from CHUYENXE
	waitfor delay '0:0:10'
	select * from CHUYENXE where MACHUYEN =@ma
commit tran
---them chuyen xe----
Create Proc usp_themchuyenxe
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as
begin 
	if(exists (select MACHUYEN from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Mã chuyến đã có'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
	insert into CHUYENXE values(@ma,@bdi,@bden,@giodi,@gioden)
	print N'Thêm Thành Công'
end

exec usp_themchuyenxe '104','A','S','21:00:00','23:59:59'
select * from CHUYENXE
----xoa chuyen xxe------
create proc usp_xoachuyenxe
@ma char(10)
as
begin
	if(not exists (select * from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Chuyến xe không tồn tại'
		return
	end
	delete DATCHO where MACHUYEN=@ma
	delete DANGKY where MACHUYEN=@ma
	delete LICHDI where MACHUYEN=@ma
	delete CHUYENXE where MACHUYEN=@ma
	print N'Xóa Thành Công'

end

exec usp_xoachuyenxe '100'
select * from lichdi
select * from CHUYENXE
--------update chuyến xe---------
Create proc usp_capnhatchuyenxe
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as 
begin
	if(not exists (select * from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Chuyến xe không tồn tại'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
		update CHUYENXE set MACHUYEN=@ma,BENXEDI=@bdi,BENXEDEN=@bden,GIODI=@giodi,GIODEN=@gioden 
		where MACHUYEN=@ma
	print N'Update thành công'

end
exec usp_capnhatchuyenxe '100','t','S21','21:00:00','23:59:59'
select * from CHUYENXE

-------Phan Tom-----------
--------dem so luong chuyen trong lich di--------
USE [QLLVT]
GO
/****** Object:  StoredProcedure [dbo].[usp_Soluongchuyen]    Script Date: 10/2/2013 6:31:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create proc [dbo].[usp_Soluongchuyen]

@chuyen nchar(20),
@ngaybd datetime,
@ngaykt datetime,
@tong int output
As
begin tran
set tran isolation level serializable
begin try
	if(not exists( select * from LICHDI where MACHUYEN=@chuyen))
	begin
		print N'Khong ton tai chuyen xe co ma la '+@chuyen
		return
	end
		
		set @tong= (select count(MACHUYEN) from LICHDI where MACHUYEN=@chuyen and NGAYDI between @ngaybd and @ngaykt)
		waitfor delay '00:00:10'
		select * from LICHDI where MACHUYEN=@chuyen and NGAYDI between @ngaybd and @ngaykt
	 
end try
BEGIN CATCH 
			DECLARE @ErrorMsg VARCHAR(2000)
			SELECT @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
			RAISERROR(@ErrorMsg, 16,1)
			ROLLBACK TRAN
			RETURN
END CATCH 
commit


declare @tong int
exec usp_Soluongchuyen '100','11/11/2013','11/27/2013', @tong out
print @tong

select * from LICHDI where MACHUYEN='100'

------insert lich di----
CREATE proc [dbo].[usp_themlich]
@Ngay datetime,
@Chuyen char(15),
@So char(15),
@loai char(15),
@sotai int,
@solo int
As
Begin
Begin try
if (not exists (select * from CHUYENXE where MACHUYEN = @Chuyen))
	begin
		print 'Mã chuyến ' +@chuyen + ' Khong Ton Tai'
		return
	end
if (exists (select * from LICHDI where @Ngay=NGAYDI AND @Chuyen=MACHUYEN AND @SO=SOHIEU AND @loai=MALOAI))
	begin
		print 'Lich di nay da co'
		return
	end
if (not exists (select * from LOAIXE where MALOAI = @loai))
	begin
		print 'Loại xe ' +@loai + ' Khong Ton Tai'
		return
	end
if (not exists (select SOHIEU from XE where MALOAI=@loai AND SOHIEU=@So))
	begin
		print ' hieu xe ko co'
		return
	end
insert into LICHDI values(@Ngay,@Chuyen,@So,@loai,@sotai,@solo, '0','NO')
END  try
BEGIN CATCH 
			DECLARE @ErrorMsg VARCHAR(2000)
			SELECT @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
			RAISERROR(@ErrorMsg, 16,1)
			ROLLBACK TRAN
			RETURN
END CATCH 
end
----------
Create Proc [dbo].[usp_sualichdi1]

@ngaydi datetime,
@machuyen char(10),
@sohieu char(10)

as
begin tran
	
	declare @tt int
	set @tt = (select sum(SOLUONG) from DATCHO where MACHUYEN=@machuyen and NGAYDI=@ngaydi
	and SOHIEU=@sohieu)
	update LICHDI set TTHIENTAI=@tt where MACHUYEN=@machuyen and NGAYDI=@ngaydi
	and SOHIEU=@sohieu
commit
----------------
Create Proc [dbo].[usp_themdatcho]
@ma char(10),
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10),
@soluong int
as
begin tran
	insert into DATCHO values (@ma,@Ngaydi,@machuyen,@sohieu,@soluong,1)
	
commit
------------
Create Proc [dbo].[usp_xoadatcho]

@ma char(10),
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10),
@soluong int
as
begin tran
	delete DATCHO where  MAKH=@ma and NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu
commit
-------------
Create proc usp_SelectALL
as
begin
	select * from CHUYENXE
end

-------------
Create proc usp_SuaChuyenXe
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as
begin
	if(not exists (select MACHUYEN from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Mã chuyến không có'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
	update CHUYENXE set BENXEDI=@bdi, BENXEDEN=@bden, GIODI=@giodi, GIODEN=@gioden where MACHUYEN=@ma 
end